package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import ModelLayer.Employee;
import ModelLayer.KeyWord;

public class DBEmployee implements IFDBEmployee {
	private Connection con;

	public DBEmployee() {
		con = DBConnection.getInstance().getDBcon();
	}

	@Override
	public Employee getEmployee(int employeeId, boolean retriveAssociation) {
		String wClause = "  employeeId = '" + employeeId + "'";
		return singleWhere(wClause, retriveAssociation);
	}

	@Override
	public int insertEmployee(Employee employee) {
		int rc = -1;
		String query = "INSERT INTO employee(employeeId, ssn, position) VALUES('"
				+ employee.getID()
				+ "','"
				+ employee.getSsn()
				+ "','"
				+ employee.getPosition().toString() + "')";
		System.out.println("insert : " + query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
			con.commit();
		} catch (SQLException ex) {
			System.out.println("Employee is not inserted");
		}
		return rc;
	}

	@Override
	public int updateEmployee(Employee employee) {
		Employee employeeObj = employee;
		int rc = -1;
		String query = "UPDATE employee SET " + "ssn = '"
				+ employeeObj.getSsn() + "'," + "position = '"
				+ employeeObj.getPosition().toString() + "'"
				+ " WHERE employeeId = '" + employeeObj.getID() + "'";
		System.out.println("Update query:" + query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch (Exception e) {
			System.out.println("Update exception in employee db: " + e);
		}
		return rc;
	}

	@Override
	public int deleteEmployee(int employeeId) {
		int rc = -1;
		String query = "DELETE FROM employee WHERE employeeId = '" + employeeId
				+ "'";
		System.out.println(query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch (Exception ex) {
			System.out.println("Delete exception in employee db: " + ex);
		}
		return rc;
	}

	@Override
	public ArrayList<Employee> getAllEmployee(boolean retrieveAssociation) {
		return miscWhere("", retrieveAssociation);
	}

	private Employee singleWhere(String wClause, boolean retrieveAssociation) {
		ResultSet results;
		Employee employee = new Employee();
		String query = buildQuery(wClause);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			if (results.next()) {
				employee = buildEmployee(results);
				stmt.close();
			} else {
				employee = null;
			}
		} catch (Exception e) {
			System.out.println("Query exception: " + e);
		}
		return employee;
	}

	private ArrayList<Employee> miscWhere(String wClause,
			boolean retrieveAssociation) {
		ResultSet results;
		ArrayList<Employee> list = new ArrayList<Employee>();
		String query = buildQuery(wClause);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			while (results.next()) {
				Employee employee = new Employee();
				employee = buildEmployee(results);
				list.add(employee);
			}
			stmt.close();
		} catch (Exception e) {
			System.out.println("Query exception - select: " + e);
			e.printStackTrace();
		}
		return list;
	}

	private Employee buildEmployee(ResultSet results) {
		Employee employee = new Employee();
		try {
			employee.setID(results.getInt("employeeId"));
			employee.setSsn(results.getString("ssn"));
			employee.setPosition(KeyWord.valueOf(results.getString("position")));
		} catch (Exception e) {
			System.out.println("error in building the employee object");
		}
		return employee;
	}

	private String buildQuery(String wClause) {
		String query = "SELECT employeeId, ssn, position FROM employee";

		if (wClause.length() > 0)
			query = query + " WHERE " + wClause;

		return query;
	}

}
